﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data.Sql;

namespace RestaurantManagerWebApp.Pages.UserManagement
{
    public partial class MainUserManagement : System.Web.UI.Page
    {
        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GridView1.DataBind();
                
            }
            

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void lblAdd_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connection);
            conn.Open();
            
            string addr = txtAddr.Text;
            string bankAcc = txtBankAcc.Text;
            DateTime dob = calendar.SelectedDate.Date;
            string email = txtEmail.Text;
            string empID = txtEmpID.Text;
            string fname = txtFname.Text;
            string hpNo = txtHP.Text;
            string lname = txtLname.Text;
            string natioanlity = ddlNationality.Text;
            string userID = txtUserID.Text;
            DateTime joinedDate = calJoinDate.SelectedDate.Date;
            string password = txtPassword.Text;
            string role = ddlRoles.Text;
            string pname = txtpname.Text;

            SqlDataReader dr = CheckDupEMP(conn, empID);
            if (dr.HasRows)
            {
                Response.Write("<script type=\"text/javascript\">alert('Duplicate Employee ID!');</script>");
            }
            else
            {
                dr.Close();
                SqlDataReader dr1 = CheckDupUserID(conn, userID);
                if (dr1.HasRows)
                {
                    Response.Write("<script type=\"text/javascript\">alert('Duplicate User ID!');</script>");

                }
                else
                {
                    dr1.Close();            
                    InsertRecord(conn, addr, bankAcc, dob, email, empID, fname, hpNo, lname, natioanlity, userID, joinedDate);
                    InsertUserAccountRecord(conn, password, userID, role, empID);
                    //if (Image1.ImageUrl != null)
                    //{
                        InsertPhotoRecord(conn, pname, Image1.ImageUrl, empID);
                    //}
                    Response.Write("<script type=\"text/javascript\">alert('Employee Record Inserted!');</script>");
                    GridView1.EditIndex = -1;
                    GridView1.DataBind();
                    ClearForm();

                    
                }
            }


            conn.Close();
        }

        private static SqlDataReader CheckDupEMP(SqlConnection conn, string empID)
        {
            string cmpEmpID = "Select emp_id from Emp_records where emp_id = @cmpEMPID";
            SqlCommand checkPK = new SqlCommand(cmpEmpID, conn);
            checkPK.Parameters.AddWithValue("@cmpEMPID", empID);
            SqlDataReader dr = checkPK.ExecuteReader();
            return dr;
        }

        private static SqlDataReader CheckDupUserID(SqlConnection conn, string userID)
        {
            string cmpUserID = "Select id_no from Emp_records where id_no = @cmpUserID";
            SqlCommand checkU = new SqlCommand(cmpUserID, conn);
            checkU.Parameters.AddWithValue("@cmpUserID", userID);
            SqlDataReader dr = checkU.ExecuteReader();
            return dr;
        }

        private void InsertRecord(SqlConnection conn, string addr, string bankAcc, DateTime dob, string email, string empID, string fname, string hpNo, string lname, string natioanlity, string userID, DateTime joinedDate)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Insert into Emp_records (id_no, emp_id, fname, lname, dob, hp_no, address, email, bank_acc, nationality, when_joined) Values (@userID, @empID, @fname, @lname, @dob, @hpNo, @addr, @email, @bankAcc, @nationality, @joinedDate )";
                cmd.Parameters.AddWithValue("@userID", userID);
                cmd.Parameters.AddWithValue("@empID", empID);
                cmd.Parameters.AddWithValue("@fname", fname);
                cmd.Parameters.AddWithValue("@lname", lname);
                cmd.Parameters.AddWithValue("@dob", dob);
                cmd.Parameters.AddWithValue("@hpNo", hpNo);
                cmd.Parameters.AddWithValue("@addr", addr);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@bankAcc", bankAcc);
                cmd.Parameters.AddWithValue("@nationality", natioanlity);
                cmd.Parameters.AddWithValue("@joinedDate", joinedDate);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Response.Write(ex.Message);
            }
        }

        private void InsertPhotoRecord(SqlConnection conn, string pname, string pathname, string empID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Insert into Photos (p_name, pathname, emp_id) Values (@pname, @pathname, @empID)";
                cmd.Parameters.AddWithValue("@pname", pname);
                cmd.Parameters.AddWithValue("@empID", empID);
                cmd.Parameters.AddWithValue("@pathname", pathname);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Response.Write(ex.Message);
            }
        }

        private void InsertUserAccountRecord(SqlConnection conn, string password, string userID, string role, string empID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Insert into Users (u_id, password, role, emp_id) Values (@userID, @password, @role, @empID)";
                cmd.Parameters.AddWithValue("@userID", userID);
                cmd.Parameters.AddWithValue("@empID", empID);
                cmd.Parameters.AddWithValue("@password", password);
                cmd.Parameters.AddWithValue("@role", role);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Response.Write(ex.Message);
            }
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {


        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {

        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {

        }

        private void ClearForm()
        {
            txtAddr.Text = "";
            txtBankAcc.Text = "";
            txtEmail.Text = "";
            txtEmpID.Text = "";
            txtFname.Text = "";
            txtHP.Text = "";
            txtLname.Text = "";
            txtUserID.Text = "";
            calJoinDate.SelectedDates.Clear();
            calendar.SelectedDates.Clear();
            txtPassword.Text = "";
            Image1.ImageUrl = "";
            txtpname.Text = "";
            ddlRoles.ClearSelection();
            ddlNationality.ClearSelection();
            txtDOB.Text = "";
            txtJDate.Text = "";
            
        }


        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {


        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {

        }

        protected void lblCancel_Click(object sender, EventArgs e)
        {
            ClearForm();
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            //Call bind gridview function
            GridView1.DataBind();
        }

        protected void calJoinDate_SelectionChanged(object sender, EventArgs e)
        {
            calJoinDate.Visible = false;
            txtJDate.Text = calJoinDate.SelectedDate.ToString("dd/MM/yyyy");
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                string fileName = FileUpload1.FileName.ToString();
                string uploadFolderPath = "~/Images/";
                string filePath = HttpContext.Current.Server.MapPath(uploadFolderPath);
                FileUpload1.SaveAs(filePath + "\\" + fileName);
                Image1.ImageUrl = "~/Images/" + "/" + FileUpload1.FileName.ToString();
                
            }
        }

        protected void calendar_SelectionChanged(object sender, EventArgs e)
        {
            calendar.Visible = false;
            txtDOB.Text = calendar.SelectedDate.ToString("dd/MM/yyyy");
        }

        protected void btnShowDOB_Click(object sender, EventArgs e)
        {
            if (calendar.Visible == true)
            {
                calendar.Visible = false;
            }
            else
            {
                calendar.Visible = true;
            }
            txtDOB.Focus();
        }

        protected void btnShowJDate_Click(object sender, EventArgs e)
        {
            if (calJoinDate.Visible == true)
            {
                calJoinDate.Visible = false;
            }
            else
            {
                calJoinDate.Visible = true;
            }
            txtJDate.Focus();
        }

        protected void lblCancelPic_Click(object sender, EventArgs e)
        {
            Image1.ImageUrl = "";
        }
    }
}